package com.liwen.kefu.service.impl;

import com.liwen.kefu.service.ActionDataService;
import com.liwen.kefu.utils.ExcelUtil;
import com.liwen.kefu.utils.JdbcUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;

import javax.annotation.Resource;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * @author: Liwen
 * @createTime: 2024/11/1
 * @description:
 */

@Service
@Slf4j
public class ActionDataServiceImpl implements ActionDataService {

    @Resource(name = "hiveJdbcTemplate")
    private JdbcTemplate hiveJdbcTemplate;

    @Autowired
    private InitDataServiceImpl initData;

    String host = "10.152.37.9";
    String port = "13316";
    String userName = "root";
    String password = "123456";


    @Override
    public String execHiveQueryUserId(String mobileNo, String type) {

        String userId = "";
        // 新核心需要查询userId，老核心不需要
        if (StringUtils.isNotEmpty(type) && !"old".equals(type)) {
            String sql = "select * from passport.t_user where mobile_no=\"${mobile_no}\";";
            String sqlFormat = sql.replaceAll("\\$\\{mobile_no\\}", mobileNo);
            try {
                log.info("【开始查询userId:{}】=================================", sqlFormat);
                Map<String, Object> map = hiveJdbcTemplate.queryForMap(sqlFormat);
                userId = (String) map.get("id");

                log.info("【userId:{}】==============================================", userId);
            } catch (DataAccessException e) {
                e.printStackTrace();
                log.error("【手机号：{}，查询userId出错了】=================================", mobileNo);
            }
        }
        return userId;
    }

    /**
     * hive查询
     *
     * @param sql
     * @param tableName
     * @return
     */
    @Override
    public List<Map<String, Object>> execHiveQuery(String sql, String tableName) {
        log.info("【开始查询hive】============================================");
        List<Map<String, Object>> list = hiveJdbcTemplate.queryForList(sql);
        log.info("【hive 查询sql：】{}", sql);
        log.info("hive:{}, 查询完成，条数：{}", tableName, list.size());
        return list;
    }

    /**
     * list数据导出 excel
     *
     * @param filePath
     * @param list
     * @return
     */
    @Override
    public String exportDataToExcel(String filePath, List<Map<String, Object>> list) {
        log.info("导出到excel：{}", filePath);
        ExcelUtil.fileExists(filePath);
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("Sheet1");
        List<String> titleList = new ArrayList<>();
        for (int i = 0; i < 1; i++) {
            Map<String, Object> map = list.get(i);
            map.forEach((k, v) -> {
                titleList.add(k);
            });
        }
        // 表头
        for (int i = 0; i < list.size(); i++) {
            Row row = sheet.createRow(i);
            for (int j = 0; j < titleList.size(); j++) {
                Cell cell = row.createCell(j);
                cell.setCellValue(titleList.get(j));
            }
        }
        for (int i = 0; i < list.size(); i++) {
            Row row = sheet.createRow(i + 1);
            Collection<Object> values = list.get(i).values();
            ArrayList<Object> valueList = new ArrayList<>(values);
            for (int j = 0; j < values.size(); j++) {
                Cell cell = row.createCell(j);
                Object value = valueList.get(j);
                if (value == null) {
                    cell.setCellValue("NULL");
                } else if ("false".equals(value.toString()) || "False".equals(value.toString())) {
                    cell.setCellValue(0);
                } else if ("true".equals(value.toString()) || "TRUE".equals(value.toString())) {
                    cell.setCellValue(1);
                } else {
                    cell.setCellValue(value + "");
                }
            }
        }
        // 将工作簿内容写入到文件输出流中
        try {
            FileOutputStream outputStream = new FileOutputStream(filePath);
            workbook.write(outputStream);
            outputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return filePath;
    }

    /**
     * 读取excel，根据key查询对应的value
     *
     * @param excelPath
     * @param key
     * @return
     * @throws IOException
     */
    @Override
    public String getDeleteKeysValue(String excelPath, String key) throws IOException {
        List<String> excelSqlData = ExcelUtil.readExcelSqlData(excelPath);
        String titles = excelSqlData.get(0);
        String[] split = titles.split(",");
        int index = 0;
        for (int i = 0; i < split.length; i++) {
            if (split[i].contains(key)) {
                index = i;
                break;
            }
        }
        List<String> idList = new ArrayList<>();
        excelSqlData.remove(0);
        for (String item : excelSqlData) {
            String[] split1 = item.split(",");
            idList.add(split1[index]);
        }
        List<String> distinctIdList = idList.stream().distinct().collect(Collectors.toList());
        String ids = String.join(",", distinctIdList);
        log.info("索引是：{}", index);
        log.info("删除的key：{},value:{}", key, ids);
        return ids;
    }


    /**
     * 根据key删除sql数据
     *
     * @param ids
     * @throws IOException
     */
    @Override
    public void execSqlDelete(String dbName, String tableName, String key, String ids) throws SQLException {
        log.info("开始删除========================================================");
        String deleteSql = "DELETE FROM " + dbName + "." + tableName + " WHERE " + key + " IN(%s)";
        String formatSql = String.format(deleteSql, ids);
        log.info("删除sql：{}", formatSql);
        int count = JdbcUtil.executeUpdate(host, port, userName, password, dbName, formatSql);
        log.info("删除条数：{}", count);
    }

    /**
     * 读取excel insert to mysql
     *
     * @param excelPath
     * @param dbName
     * @param tableName
     * @throws IOException
     */
    @Override
    public void execMysqlInsert(String excelPath, String dbName, String tableName) throws IOException, SQLException {
        log.info("【开始插入mysql】==============================================");
        List<String> excelSqlData = ExcelUtil.readExcelSqlData(excelPath);
        String title = excelSqlData.get(0);
        String sql = "INSERT INTO " + dbName + "." + tableName + " (" + title + ")" + " VALUES (%s);";
        excelSqlData.remove(0);
        if (!excelSqlData.isEmpty()) {
            for (String item : excelSqlData) {
                String formatSql = String.format(sql, item);
                System.out.println(formatSql);
                JdbcUtil.executeUpdate(host, port, userName, password, dbName, formatSql);
            }
        }

    }

    /**
     * 查询mysql导入结果
     *
     * @param key
     * @param ids
     */
    @Override
    public void queryImportResult(String dbName, String tableName, String key, String ids) {
        log.info("【开始查询导入结果】========================================");
        String sql = "SELECT * FROM " + dbName + "." + tableName + " WHERE " + key + " IN(%s)";
        String formatSql = String.format(sql, ids);
        log.info("查询SQL：{}", formatSql);
        List<String> resultList = JdbcUtil.executeQuery(host, port, userName, password, dbName, formatSql, key);
        if (CollectionUtils.isEmpty(resultList)) {
            log.error("查询结果为空：key:{},resultList:{}", key, resultList);
        } else {
            log.info("查询key:{},查询条数：{}", key, resultList.size());
            log.info("【查询结果】：resultList：{}", resultList);
        }
    }

    /**
     * 1、根据key获取excel 的value
     * 2、根据value 删除mysql
     * 3、插入mysql
     *
     * @param dbName
     * @param tableName
     * @param filePath
     */
    @Override
    public String excelImportDataToMysql(String dbName, String tableName, String filePath) {
        String key = initData.initKeys(dbName, tableName);
        String msg = "success";
        String ids = "";
        try {
            ids = getDeleteKeysValue(filePath, key);
        } catch (IOException e) {
            e.printStackTrace();
            msg = "【excel 获取删除的values 失败了】===========================================";
            log.error(msg);
            return msg;
        }

        try {
            execSqlDelete(dbName, tableName, key, ids);
        } catch (SQLException e) {
            e.printStackTrace();
            msg = "【删除mysql 失败了:key :{},value:{}】===========================================";
            log.error(msg, key, ids);
            return msg;
        }

        try {
            execMysqlInsert(filePath, dbName, tableName);
        } catch (SQLException | IOException e) {
            e.printStackTrace();
            msg = "【插入mysql数据失败了】===========================================";
            log.error(msg);
            return msg;
        }
        queryImportResult(dbName, tableName, key, ids);
        return msg;
    }

    @Override
    public void execSql(String dbName, String formatSql) {
        try {
            int count = JdbcUtil.executeUpdate(host, port, userName, password, dbName, formatSql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


}
